package com.lemon.components.docs;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import com.lemon.components.ant.beans.ProjectInfo;
import com.lemon.components.ant.util.JdbcUtil;
import com.lemon.components.docs.mysql.MysqlLocalTool;
import com.system.core.security.NameUtil;

public class MysqlDbDoc {
	
	HSSFCellStyle style1 = null;
	HSSFCellStyle headStyle = null;
	
	public void docs(String dbName,String savePath,ProjectInfo projectInfo) {
		Connection conn = MysqlLocalTool.getCon(projectInfo);
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		style1 = workbook.createCellStyle();
		style1.setAlignment(HorizontalAlignment.LEFT);
		style1.setVerticalAlignment(VerticalAlignment.CENTER);
		style1.setWrapText(true);
		style1.setBorderBottom(BorderStyle.THIN);
		style1.setBorderTop(BorderStyle.THIN);
		style1.setBorderRight(BorderStyle.THIN);
		style1.setBorderLeft(BorderStyle.THIN);
		HSSFFont font = workbook.createFont();
		font.setFontName("Monaco");
		font.setFontHeightInPoints((short) 11);
		style1.setFont(font);
		
		headStyle = workbook.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		headStyle.setWrapText(true);
		headStyle.setBorderBottom(BorderStyle.THIN);
		headStyle.setBorderTop(BorderStyle.THIN);
		headStyle.setBorderRight(BorderStyle.THIN);
		headStyle.setBorderLeft(BorderStyle.THIN);
		headStyle.setFillForegroundColor(IndexedColors.DARK_TEAL.getIndex());
		headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		HSSFFont headFont = workbook.createFont();
		headFont.setColor(HSSFColorPredefined.WHITE.getIndex());
		headFont.setFontName("微软雅黑");
		headFont.setFontHeightInPoints((short) 12);
		headFont.setBold(true);
		headStyle.setFont(headFont);
		
		
		
		StringBuilder tablesSql = new StringBuilder("SELECT * FROM `TABLES` WHERE TABLE_SCHEMA='");
		tablesSql.append(dbName);
		tablesSql.append("' GROUP BY TABLE_NAME");
		PreparedStatement pstmt;
		try {
	        pstmt = conn.prepareStatement(tablesSql.toString());
	        ResultSet resultSet = pstmt.executeQuery();
	        while (resultSet.next()) {
            	String tableName = resultSet.getString("TABLE_NAME");
            	String tableComment = resultSet.getString("TABLE_COMMENT");
            	HSSFSheet sheet = workbook.createSheet(tableName+(StringUtils.isBlank(tableComment)?"":"("+tableComment+")"));
            	//窗口冻结
            	sheet.createFreezePane(255, 2);
            	//合并单元格
            	//起始行号，终止行号， 起始列号，终止列号
            	CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
            	sheet.addMergedRegion(region);
            	table(sheet,tableName,dbName,tableComment,conn);
	        }
	        workbook.write(new File(savePath+"/"+dbName+".xlsx"));
        	workbook.close();
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	}
	
	public void table(HSSFSheet sheet,String tableName,String dbName,String tableComment,Connection conn) {
		String columnsSql = "SELECT * FROM `COLUMNS` WHERE TABLE_NAME = '"+tableName+"' AND TABLE_SCHEMA = '"+dbName+"';";
		sheet.setColumnWidth(0, 40*256);
		sheet.setColumnWidth(1, 30*256);
		sheet.setColumnWidth(2, 20*256);
		sheet.setColumnWidth(3, 30*256);
		sheet.setColumnWidth(4, 20*256);
		
		HSSFRow titleRow = sheet.createRow(0);
		titleRow.setHeight((short)600);
		HSSFCell titleCell = titleRow.createCell(0);
		titleCell.setCellValue((StringUtils.isBlank(tableComment)?tableName:tableComment)+"("+ tableName+")");
		titleCell.setCellStyle(headStyle);
		
		HSSFRow headRow = sheet.createRow(1);
		headRow.setHeight((short)600);
		
		HSSFCell cell1 = headRow.createCell(0);
		cell1.setCellValue("项目");
		cell1.setCellStyle(headStyle);
		
		HSSFCell cell2 = headRow.createCell(1);
		cell2.setCellValue("编码");
		cell2.setCellStyle(headStyle);
		
		HSSFCell cell3 = headRow.createCell(2);
		cell3.setCellValue("字段类型");
		cell3.setCellStyle(headStyle);
		
		HSSFCell cell4 = headRow.createCell(3);
		cell4.setCellValue("JavaCode");
		cell4.setCellStyle(headStyle);
		
		HSSFCell cell5 = headRow.createCell(4);
		cell5.setCellValue("JavaType");
		cell5.setCellStyle(headStyle);
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(columnsSql);
			ResultSet resultSet = pstmt.executeQuery();
			int rowNumber = 2;
			while(resultSet.next()) {
				HSSFRow row = sheet.createRow(rowNumber);
				row.setHeight((short)600);
				HSSFCell c1 = row.createCell(0);
				c1.setCellValue(resultSet.getString("COLUMN_COMMENT"));
				c1.setCellStyle(style1);
				
				HSSFCell c2 = row.createCell(1);
				c2.setCellValue(resultSet.getString("COLUMN_NAME"));
				c2.setCellStyle(style1);
				
				HSSFCell c3 = row.createCell(2);
				c3.setCellValue(resultSet.getString("COLUMN_TYPE"));
				c3.setCellStyle(style1);
				
				HSSFCell c4 = row.createCell(3);
				c4.setCellValue(NameUtil._2JavaName(resultSet.getString("COLUMN_NAME")));
				c4.setCellStyle(style1);
				
				HSSFCell c5 = row.createCell(4);
				c5.setCellValue(JdbcUtil.typeCoverJdbc2Java(resultSet.getString("DATA_TYPE")));
				c5.setCellStyle(style1);
				rowNumber ++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		ProjectInfo projectInfo = new ProjectInfo();
		projectInfo.setDbhost("172.31.40.13:3307");
		projectInfo.setDbusername("admin");
		projectInfo.setDbpasswd("admin123321");
		projectInfo.setDbname("information_schema");
		MysqlDbDoc mysqlDbDoc = new MysqlDbDoc();
		mysqlDbDoc.docs("d_cmf_payment","d:",projectInfo);
	}

}
